﻿/*----------------------------------------------------
 * Author : Tharinduni Udugamasuriya
 * FinCon dll for Handeling DB 
 * DB Connection Handeling Class
 ---------------------------------------------------*/

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DSPL.Common
{
    public class DBConnection
    {
        SqlConnection finCon;

        public DataTable getDbList(string DataSource, string Username, string Password)
        {
            DataTable dtDbList = new DataTable();

            OpenConnection(DataSource, Username, Password, false);
            SqlDataAdapter dap = new SqlDataAdapter("SELECT name FROM master..sysdatabases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')", finCon);
            dap.Fill(dtDbList);
            dap.Dispose();
            CloseConnection();

            return dtDbList;
        }

        private void OpenConnection(string DataSource, string Username, string Password, bool isDbAvail, string Database = null)
        {
            SqlConnectionStringBuilder finConstr = new SqlConnectionStringBuilder();
            finConstr.DataSource = DataSource;
            finConstr.UserID = Username;
            finConstr.Password = Password;
            finConstr.IntegratedSecurity = true;
            if (isDbAvail)
            {
                finConstr.InitialCatalog = Database;
            }
            finCon = new SqlConnection(finConstr.ToString());
            finCon.Open();
        }

        private void CloseConnection()
        {
            if (finCon.State != ConnectionState.Closed)
            {
                finCon.Close();
            }
        }

        public bool ConnectionSuccess(string DataSource, string Username, string Password, string Database)
        {
            OpenConnection(DataSource, Username, Password, true, Database);
            if (finCon.State != ConnectionState.Open)
            {
                return false;
            }
            return true;
        }

        public string BuildConnectionString(string DataSource, string Username, string Password, bool isDbAvail, string Database = null)
        {
            SqlConnectionStringBuilder finConstr = new SqlConnectionStringBuilder();
            finConstr.DataSource = DataSource;
            finConstr.UserID = Username;
            finConstr.Password = Password;
            finConstr.IntegratedSecurity = true;
            if (isDbAvail)
            {
                finConstr.InitialCatalog = Database;
            }
            return finConstr.ToString();
        }
    }

}
